﻿using System;
using System.Data;
using System.Data.SQLite;
using System.IO;
using System.Windows.Forms;

namespace _6.Util
{
    public class SQLite
    {
        private SQLiteConnection connection;
        private static string path = Application.StartupPath + "\\Util\\database.sqlite";

        public void InitializeDatabase()
        {
            if (!File.Exists(path))
            {
                SQLiteConnection.CreateFile(path);
                connection = new SQLiteConnection($"Data Source={path};Version=3;");
                connection.Open();
                string query =
                    "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT, password TEXT)";
                SQLiteCommand command = new SQLiteCommand(query, connection);
                command.ExecuteNonQuery();
            }
            else
            {
                connection = new SQLiteConnection($"Data Source={path};Version=3;");
                connection.Open();
            }
        }

        public void CloseConnection(SQLiteConnection connection)
        {
            connection.Close();
        }

        public string InsertToDatabase(Object obj)
        {
            var query = "";
            switch (obj)
            {
                case Model.StudentInfo student:
                    query =
                        $"INSERT INTO StudentInfo (studentID, sex, name, classID, schoolID) VALUES ({student.StudentId}, '{student.Sex}','{student.Name}', {student.ClassId}, {student.SchoolId})";
                    break;
                case Model.ClassInfo classInfo:
                    query =
                        $"INSERT INTO ClassInfo (classID, name, schoolID, count) VALUES ({classInfo.ClassId}, '{classInfo.Name}', {classInfo.SchoolId}, {classInfo.Count})";
                    break;
                case Model.SchoolInfo schoolInfo:
                    query =
                        $"INSERT INTO SchoolInfo (schoolID, name) VALUES ({schoolInfo.SchoolId}, '{schoolInfo.Name}')";
                    break;
                default:
                    throw new Exception("Invalid object type");
            }

            return query;
        }
        public string UpdateDatabase(Object obj)
        {
            var query = "";
            switch (obj)
            {
                case Model.StudentInfo student:
                    query =
                        $"UPDATE StudentInfo SET name = '{student.Name}', classID = {student.ClassId}, schoolID = {student.SchoolId} WHERE studentID = {student.StudentId}";
                    break;
                case Model.ClassInfo classInfo:
                    query =
                        $"UPDATE ClassInfo SET name = '{classInfo.Name}', schoolID = {classInfo.SchoolId}, count = {classInfo.Count} WHERE classID = {classInfo.ClassId}";
                    break;
                case Model.SchoolInfo schoolInfo:
                    query =
                        $"UPDATE SchoolInfo SET name = '{schoolInfo.Name}' WHERE schoolID = {schoolInfo.SchoolId}";
                    break;
                default:
                    throw new Exception("Invalid object type");
            }

            return query;
        }
        public string DeleteFromDatabase(Object obj)
        {
            var query = "";
            switch (obj)
            {
                case Model.StudentInfo student:
                    query = $"DELETE FROM StudentInfo WHERE studentID = {student.StudentId}";
                    break;
                case Model.ClassInfo classInfo:
                    query = $"DELETE FROM ClassInfo WHERE classID = {classInfo.ClassId}";
                    break;
                case Model.SchoolInfo schoolInfo:
                    query = $"DELETE FROM SchoolInfo WHERE schoolID = {schoolInfo.SchoolId}";
                    break;
                default:
                    throw new Exception("Invalid object type");
            }

            return query;
        }
        public string SelectFromDatabase(Object obj)
        {
            var query = "";
            switch (obj)
            {
                case Model.StudentInfo _:
                    query = $"SELECT * FROM StudentInfo";
                    break;
                case Model.ClassInfo _:
                    query = $"SELECT * FROM ClassInfo";
                    break;
                case Model.SchoolInfo _:
                    query = $"SELECT * FROM SchoolInfo";
                    break;
                default:
                    throw new Exception("Invalid object type");
            }
            return query;
        }
        public void ExecuteQuery(string query)
        {
            SQLiteCommand command = new SQLiteCommand(query, connection);
            command.ExecuteNonQuery();
        }
        public DataTable Adapter(string query)
        {
            SQLiteDataAdapter adapter = new SQLiteDataAdapter(query, connection);
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            return dataTable;
        }
    }
}